package com.lanyou.esb.repository;

import java.util.List;

import org.apache.shiro.authz.annotation.Logical;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RestResource;

import com.lanyou.esb.entity.StoreForward;


public interface StoreForwardRepository extends Repository<StoreForward, String>, JpaSpecificationExecutor<StoreForward> {
	
	@Query(nativeQuery = true, value="SELECT t3.FIELD_NAME,f.FIELD_ID,f.FIELD_SYSTEM_ID,"
			+ "t2.SYSTEM_NAME,f.IF_NAME,f.FORWARD_STATUS,f.times "
			+ " FROM ("
			+ " SELECT t1.FIELD_ID,t1.IF_ID,t1.FIELD_SYSTEM_ID,t1.IF_NAME,t1.FORWARD_STATUS,SUM(t1.FORWARD_TIMES) AS times"
			+ " FROM t_esb_bu_store_forward t1"
			+ " WHERE( '' = :fieldId OR t1.FIELD_ID = :fieldId )"
			+ " AND ('' = :systemId OR t1.FIELD_SYSTEM_ID = :systemId )"
			+ " AND ('' = :ifName OR t1.IF_ID = :ifName )"
			+ " AND ('' = :status OR t1.FORWARD_STATUS = :status)"
			+ " AND (''= :beginDate OR t1.FORWARD_DATE BETWEEN :beginDate AND :endDate)"
			+ " AND ('' = :userId OR EXISTS"
			+ " (SELECT t4.FIELD_SYSTEM_ID FROM t_cook_bu_user_system t4 "
			+ " WHERE ('' = :userId OR t4.USER_ID = :userId)))"
			+ " GROUP BY t1.IF_ID,t1.FORWARD_STATUS) f"
			+ " LEFT JOIN t_cook_bu_field_system t2 ON f.FIELD_SYSTEM_ID = t2.FIELD_SYSTEM_ID"
			+ " LEFT JOIN t_cook_db_field t3 ON f.FIELD_ID=t3.FIELD_ID"
			+ " LIMIT :page1,:page2")
	List<Object[]> countStoreForward(@Param("userId") String userId,
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId,
			@Param("ifName") String ifName,
			@Param("beginDate") String beginDate,
			@Param("endDate") String endDate,
			@Param("status") String status,
			@Param("page1") int page1,
			@Param("page2") int page2);
	
	
	@Query(nativeQuery = true, value="SELECT t3.FIELD_NAME,f.FIELD_ID,f.FIELD_SYSTEM_ID,"
			+ "t2.SYSTEM_NAME,f.IF_NAME,f.FORWARD_STATUS,f.times "
			+ " FROM ("
			+ " SELECT t1.FIELD_ID,t1.IF_ID,t1.FIELD_SYSTEM_ID,t1.IF_NAME,t1.FORWARD_STATUS,SUM(t1.FORWARD_TIMES) AS times"
			+ " FROM t_esb_bu_store_forward t1"
			+ " WHERE( '' = :fieldId OR t1.FIELD_ID = :fieldId )"
			+ " AND ('' = :systemId OR t1.FIELD_SYSTEM_ID = :systemId )"
			+ " AND ('' = :ifName OR t1.IF_ID = :ifName )"
			+ " AND ('' = :status OR t1.FORWARD_STATUS = :status)"
			+ " AND (''= :beginDate OR t1.FORWARD_DATE BETWEEN :beginDate AND :endDate)"
			+ " AND ('' = :userId OR EXISTS"
			+ " (SELECT t4.FIELD_SYSTEM_ID FROM t_cook_bu_user_system t4 "
			+ " WHERE ('' = :userId OR t4.USER_ID = :userId)))"
			+ " GROUP BY t1.IF_ID,t1.FORWARD_STATUS) f"
			+ " LEFT JOIN t_cook_bu_field_system t2 ON f.FIELD_SYSTEM_ID = t2.FIELD_SYSTEM_ID"
			+ " LEFT JOIN t_cook_db_field t3 ON f.FIELD_ID=t3.FIELD_ID")
	List<Object[]> totalCountStoreForward(@Param("userId") String userId,	
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId,
			@Param("ifName") String ifName,
			@Param("beginDate") String beginDate,
			@Param("endDate") String endDate,
			@Param("status") String status);
	
	long count();

	boolean exists(String id);

	@RestResource(exported = true)
	<S extends StoreForward> S save(S entity); // could expose REST API

	<S extends StoreForward> List<S> save(Iterable<S> entities);

	@RestResource(exported = true)
//	@RequiresPermissions(value={"HttpManagement:view","HttpManagement:maintain"},logical=Logical.OR)
	StoreForward findOne(String id); // could expose REST API

	StoreForward getOne(String id);

	// 以下三个接口只能选一个暴露为REST API

	@RestResource(exported = false)
	List<StoreForward> findAll(); // could expose REST API

	@RestResource(exported = false)
	List<StoreForward> findAll(Sort sort); // could expose REST API

	@RestResource(exported = true)
	//@RequiresPermissions(value={"HttpManagement:view","HttpManagement:maintain"},logical=Logical.OR)
	Page<StoreForward> findAll(Pageable pageable); // could expose REST API

	List<StoreForward> findAll(Iterable<String> ids);

	// 以下两个接口，要么都暴露为REST API，要么都不暴露
	@RestResource(exported = true)
	// @RequiresPermissions(value={"HttpManagement:maintain"})
	void delete(String id); // could expose REST API

	@RestResource(exported = true)
	// @RequiresPermissions(value={"HttpManagement:maintain"})
	void delete(StoreForward entity); // could expose REST API

	void delete(Iterable<? extends StoreForward> entities);

	void deleteAll();

	void flush();

	<S extends StoreForward> S saveAndFlush(S entity);

	void deleteInBatch(Iterable<StoreForward> entities);

	void deleteAllInBatch();

	
	
}